package com.imis.base.util.table;

import com.imis.base.constant.CommonConstant;
import com.imis.base.constant.DataBaseConstant;
import com.imis.base.constant.enums.ArgumentResponseEnum;
import com.imis.base.constant.enums.CommonResponseEnum;
import com.imis.base.globle.exception.BusinessException;
import com.imis.base.util.ConvertUtils;
import com.imis.base.util.FreemarkerHelper;
import com.imis.base.util.table.model.*;
import com.imis.base.util.table.service.IDataBaseTableHandleService;
import com.imis.module.online.table.model.po.TableFields;
import com.imis.module.online.table.model.po.TableIndex;
import lombok.extern.slf4j.Slf4j;
import org.assertj.core.util.Lists;
import org.hibernate.HibernateException;
import org.hibernate.boot.Metadata;
import org.hibernate.boot.MetadataSources;
import org.hibernate.boot.registry.StandardServiceRegistryBuilder;
import org.hibernate.service.ServiceRegistry;
import org.hibernate.tool.hbm2ddl.SchemaExport;
import org.hibernate.tool.schema.TargetType;

import java.io.ByteArrayInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.*;

/**
 * <p>
 * DataBaseTableProcess<br>
 * 通过 Hibernate 和脚本来处理来同步数据库
 * 对于修改数据库的字段，考虑各种数据库的情况，字段名称全部、类型修改成大写
 * </p>
 *
 * @author XinLau
 * @since 2020-03-12
 */
@Slf4j
public class DataBaseTableProcess {

    private static IDataBaseTableHandleService dataBaseTableHandleService;

    public DataBaseTableProcess() throws SQLException, BusinessException {
        dataBaseTableHandleService = DataBaseTableUtil.getTableHandle();
    }

    /**
     * 获取模板所需参数
     *
     * @param tableFormConfigModel - 数据库表的表单配置模型
     * @param dataBaseType         - 数据库类型
     * @return TableTemplateModel - 数据库表模板模型
     * @author XinLau
     * @creed The only constant is change ! ! !
     * @since 2020/10/3 17:17
     */
    private static TableTemplateModel getTableTemplateModel(TableFormConfigModel tableFormConfigModel, String dataBaseType) {
        return TableTemplateConverter.INSTANCE.modelConvert(tableFormConfigModel, dataBaseType);
    }

    /**
     * 获取原表列数据
     *
     * @param schemaName -
     * @param tableName  -
     * @return Map<String, ColumnMeta> -
     * @author XinLau
     * @creed The only constant is change ! ! !
     * @since 2020/10/4 10:24
     */
    private Map<String, ColumnMeta> getColumnMetaDataFormDataBase(String schemaName, String tableName) throws SQLException {
        ArgumentResponseEnum.NULL_POINTER_EXCEPTION.assertNotEmpty(schemaName);
        ArgumentResponseEnum.NULL_POINTER_EXCEPTION.assertNotEmpty(tableName);
        Map<String, ColumnMeta> columnMap = new HashMap<>(16);
        Connection connection = null;
        try {
            connection = DataBaseTableUtil.getConnection();
        } catch (Exception e) {
            log.error(e.getMessage(), e);
        }
        ArgumentResponseEnum.NULL_POINTER_EXCEPTION.assertNotNull(connection);
        DatabaseMetaData dataBaseMetaData = connection.getMetaData();
        ArgumentResponseEnum.NULL_POINTER_EXCEPTION.assertNotNull(dataBaseMetaData);
        ResultSet resultSet = dataBaseMetaData.getColumns(null, schemaName, tableName, "%");
        ColumnMeta columnMeta;
        while (resultSet.next()) {
            columnMeta = new ColumnMeta();
            columnMeta.setTableName(tableName);

            String columnName = resultSet.getString("COLUMN_NAME").toLowerCase();
            columnMeta.setColumnName(columnName);

            String typeName = resultSet.getString("TYPE_NAME");
            int decimalDigits = resultSet.getInt("DECIMAL_DIGITS");

            String columnType = dataBaseTableHandleService.getMatchClassTypeByDataType(typeName, decimalDigits);
            columnMeta.setColumnType(columnType);

            int columnSize = resultSet.getInt("COLUMN_SIZE");
            columnMeta.setColumnSize(columnSize);

            columnMeta.setDecimalDigits(decimalDigits);
            String isNullable = resultSet.getInt("NULLABLE") == 1 ? CommonConstant.TO_UPPER_CASE_Y : CommonConstant.TO_UPPER_CASE_N;
            columnMeta.setIsNullable(isNullable);

            String columnComment = resultSet.getString("REMARKS");
            columnMeta.setColumnComment(columnComment);

            String columnDefaultValue = resultSet.getString("COLUMN_DEF");
            String fieldDefaultValue = ConvertUtils.judgeIsNumber(columnDefaultValue) == null ? "" : ConvertUtils.judgeIsNumber(columnDefaultValue);
            columnMeta.setColumnDefaultValue(fieldDefaultValue);
            log.info("getColumnMetaDataFormDataBase ---> COLUMN_NAME:" + columnName.toUpperCase() + " TYPE_NAME :" + typeName
                    + " DECIMAL_DIGITS:" + decimalDigits + " COLUMN_SIZE:" + columnSize);
            columnMap.put(columnName, columnMeta);
        }
        return columnMap;
    }

    /**
     * 返回 数据库表字段信息 中列名的新和旧的对应关系
     *
     * @param fieldList - 数据库表字段信息
     * @return Map<field.getFieldName ( ), field.getFieldNameOld ( )> -
     * @author XinLau
     * @creed The only constant is change ! ! !
     * @since 2020/10/4 10:41
     */
    private Map<String, String> getNewAndOldFieldName(List<TableFields> fieldList) {
        Map<String, String> map = new HashMap<String, String>(fieldList.size());
        for (TableFields field : fieldList) {
            map.put(field.getFieldName(), field.getFieldNameOld());
        }
        return map;
    }

    /**
     * 获取更新旧字段SQL
     *
     * @param columnName - 字段名
     * @param id         - 字段编号
     * @return String - 更新旧字段SQL
     * @author XinLau
     * @creed The only constant is change ! ! !
     * @since 2020/10/4 14:35
     */
    private String getUpdateOldFieldSql(String columnName, String id) {
        return "UPDATE onl_table_fields SET field_name_old = '" + columnName + "' WHERE ID = '" + id + "'";
    }

    /**
     * 获取创建更新字段的SQL
     *
     * @param formColumnMeta - Form 的自动配置信息
     * @param dataColumnMeta - 数据库表处理对象
     * @return String - 创建更新字段的SQL
     * @author XinLau
     * @creed The only constant is change ! ! !
     * @since 2020/10/4 14:37
     */
    private String getUpdateColumnSql(ColumnMeta formColumnMeta, ColumnMeta dataColumnMeta) {
        // modify birthday varchar2(10) not null;
        // return " MODIFY COLUMN  "+getFieldDesc(columnMeta)+",";
        return dataBaseTableHandleService.getUpdateColumnSql(formColumnMeta, dataColumnMeta);
    }

    /**
     * 获取处理特殊的SQL
     *
     * @param formColumnMeta - Form 的自动配置信息
     * @param dataColumnMeta - 数据库表处理对象
     * @return String - 处理特殊的SQL
     * @author XinLau
     * @creed The only constant is change ! ! !
     * @since 2020/10/4 14:37
     */
    private String getUpdateSpecialSql(ColumnMeta formColumnMeta, ColumnMeta dataColumnMeta) {
        return dataBaseTableHandleService.getSpecialHandle(formColumnMeta, dataColumnMeta);
    }

    /**
     * 获取添加注释的SQL
     *
     * @param columnMeta - 数据库表处理对象
     * @return String - 添加注释的SQL
     * @author XinLau
     * @creed The only constant is change ! ! !
     * @since 2020/10/4 14:40
     */
    private String getColumnCommentSql(ColumnMeta columnMeta) {
        return dataBaseTableHandleService.getColumnCommentSql(columnMeta);
    }

    /**
     * 获取增加字段的SQL
     *
     * @param columnMeta - 数据库表处理对象
     * @return String - 增加字段的SQL
     * @author XinLau
     * @creed The only constant is change ! ! !
     * @since 2020/10/4 14:40
     */
    private String getAddColumnSql(ColumnMeta columnMeta) {
        // return " ADD COLUMN "+getFieldDesc(columnMeta)+",";
        return dataBaseTableHandleService.getAddColumnSql(columnMeta);
    }

    /**
     * 获取删除字段的SQL
     *
     * @param fieldName - 数据库表字段名称
     * @return String - 删除字段的SQL
     * @author XinLau
     * @creed The only constant is change ! ! !
     * @since 2020/10/4 14:40
     */
    private String getDropColumnSql(String fieldName) {
        // ALTER TABLE `test` DROP COLUMN `aaaa`;
        return dataBaseTableHandleService.getDropColumnSql(fieldName);
    }

    /**
     * 创建表
     *
     * @param tableFormConfigModel - 数据库表的表单配置模型
     * @throws IOException
     * @throws HibernateException
     * @throws SQLException
     * @throws BusinessException
     * @author XinLau
     * @creed The only constant is change ! ! !
     * @since 2020/10/3 17:13
     */
    public static void createTable(TableFormConfigModel tableFormConfigModel) throws IOException, HibernateException, SQLException {
        // 1、转换模板获取建表配置信息
        String dataBaseType = DataBaseTableUtil.getDataBaseType();
        // 1.1针对 Oracle 数据库，数字类型统一采用 Number类型实现
        if (DataBaseConstant.DB_TYPE_ORACLE.equals(dataBaseType)) {
            // 针对 Oracle 数据库，数字类型统一采用 Number方式处理
            List<TableFields> newFields = Lists.newArrayList();
            for (TableFields tableFields : tableFormConfigModel.getColumns()) {
                if (DataBaseConstant.INT.equals(tableFields.getFieldType())) {
                    tableFields.setFieldType(DataBaseConstant.DOUBLE);
                    tableFields.setFieldLength(0);
                }
                newFields.add(tableFields);
            }
            tableFormConfigModel.setColumns(newFields);
        }
        // 1.2获取数据库参数模版
        String xml = FreemarkerHelper.parseTemplateForTable(getTableTemplateModel(tableFormConfigModel, dataBaseType));
        log.info(xml);
        // 3.获取配置信息
        Map<String, Object> settings = new HashMap<>(13);
        TableDataBaseConfig dataBaseConfig = tableFormConfigModel.getTableDataBaseConfig();
        settings.put("hibernate.connection.driver_class", dataBaseConfig.getDriverClassName());
        settings.put("hibernate.connection.url", dataBaseConfig.getUrl());
        settings.put("hibernate.connection.username", dataBaseConfig.getUsername());
        settings.put("hibernate.connection.password", dataBaseConfig.getPassword());
        settings.put("hibernate.show_sql", true);
        settings.put("hibernate.format_sql", true);
        // 解决 "org.postgresql.jdbc.PgConnection.createClob() 方法尚未被实作" 问题
        settings.put("hibernate.temp.use_jdbc_metadata_defaults", false);
        settings.put("hibernate.dialect", DataBaseTableUtil.getDataBaseDialect(dataBaseType));
        /*
         * create：表示启动的时候先drop，再create
         * create-drop: 也表示创建，只不过再系统关闭前执行一下drop
         * update: 这个操作启动的时候会去检查schema是否一致，如果不一致会做scheme更新
         * validate: 启动时验证现有schema与你配置的hibernate是否一致，如果不一致就抛出异常，并不做更新
         */
        // 自动创建表
        settings.put("hibernate.hbm2ddl.auto", "create");
        // Oracle 数据库需要指定 schema
        // settings.put("hibernate.default_schema", DataBaseConstant.DATA_SOURCE_BUSINESS);
        // 用于设置事务提交方式
        settings.put("hibernate.connection.autocommit", false);
        settings.put("hibernate.current_session_context_class", "thread");
        // 4.注册连接信息
        ServiceRegistry standardServiceRegistry = new StandardServiceRegistryBuilder().applySettings(settings).build();
        // 使用指定的注册连接信息创建元数据源
        MetadataSources metadataSources = new MetadataSources(standardServiceRegistry);
        // 5.添加建表配置
        InputStream xmlInputStream = new ByteArrayInputStream(xml.getBytes());
        // 从建表配置XML中读取元数据
        metadataSources.addInputStream(xmlInputStream);
        Metadata metadata = metadataSources.buildMetadata();
        // 6.执行 SchemaExport
        SchemaExport dataBaseExport = new SchemaExport();
        dataBaseExport.create(EnumSet.of(TargetType.DATABASE), metadata);
        // TODO:This：关闭流 如果上面的异常了,这流就管不了了？
        xmlInputStream.close();

        // 7.抛出执行异常，抛出第一个即可
        @SuppressWarnings("unchecked")
        List<Exception> exceptionList = dataBaseExport.getExceptions();
        for (Exception exception : exceptionList) {
            // TODO:This：临时处理，oracle/sqlserver下同步数据库抛出异常，据测试，是同步时先删除表,但是此时没有表，不知道为啥
            if ("java.sql.SQLSyntaxErrorException".equals(exception.getCause().getClass().getName())) {
                java.sql.SQLSyntaxErrorException e = (SQLSyntaxErrorException) exception.getCause();
                if ("42000".equals(e.getSQLState())) {
                    continue;
                }
            } else if ("com.microsoft.sqlserver.jdbc.SQLServerException".equals(exception.getCause().getClass().getName())) {
                log.error(exception.getMessage());
                CommonResponseEnum.ERROR_500.assertIsTrue(exception.getCause().toString().indexOf("Incorrect syntax near the keyword") != 1, exception.getCause().getMessage());
                continue;
            }
            CommonResponseEnum.ERROR_500.assertFail(exception.getCause().getMessage());
        }
    }

    /**
     * 修改列
     *
     * @param tableFormConfigModel - 数据库表的表单配置模型
     * @return List<String> -
     * @throws BusinessException -
     * @throws SQLException      -
     * @author XinLau
     * @creed The only constant is change ! ! !
     * @since 2020/10/4 10:22
     */
    public List<String> updateTable(TableFormConfigModel tableFormConfigModel) throws SQLException {
        String dataBaseType = DataBaseTableUtil.getDataBaseType();
        String tableName = DataBaseTableUtil.getTableName(tableFormConfigModel.getTableName(), dataBaseType);
        String alterSql = DataBaseConstant.SQL_ALTER +  DataBaseConstant.SQL_TABLE + tableName + DataBaseConstant.SPACE;
        List<String> alterList = new ArrayList<String>();
        // 对表的修改列和删除列的处理，解决hibernate没有该机制
        try {
            Map<String, ColumnMeta> dataBaseColumnMeta = getColumnMetaDataFormDataBase(null, tableName);
            Map<String, ColumnMeta> tableFormColumnMeta = TableTemplateConverter.INSTANCE.modelConvert(tableFormConfigModel);
            Map<String, String> newAndOldFieldMap = getNewAndOldFieldName(tableFormConfigModel.getColumns());
            for (String columnName : tableFormColumnMeta.keySet()) {
                // 遍历配置表 判断原表列是否包含当前列
                if (!dataBaseColumnMeta.containsKey(columnName)) {
                    // 表如果不存在该列，则要对表做修改、增加、删除该列动作 此处无法处理删除的列，因为在这个循环中无法获得该列
                    // 如果旧列中包含这个列名，说明是修改名称的
                    ColumnMeta formColumnMeta = tableFormColumnMeta.get(columnName);
                    String oldFieldName = newAndOldFieldMap.get(columnName);
                    if (newAndOldFieldMap.containsKey(columnName) && (dataBaseColumnMeta.containsKey(oldFieldName))) {
                        ColumnMeta dataColumnMeta = dataBaseColumnMeta.get(oldFieldName);
                        String changeSql = dataBaseTableHandleService.getReNameFieldName(formColumnMeta);
                        if (DataBaseConstant.DB_TYPE_SQL_SERVER.equals(dataBaseType)) {
                            // SqlServer 修改类名称需要调用存储过程
                            alterList.add(changeSql);
                        } else {
                            alterList.add(alterSql + changeSql);
                        }
                        // 执行完成之后修改成一致  fieldName 和 oldfieldName
                        String oldFieldSql = getUpdateOldFieldSql(columnName, formColumnMeta.getColumnId());
                        alterList.add(oldFieldSql);
                        // updateFieldName(columnName, formColumnMeta.getColumnId(), session);
                        // 修改表名之后继续判断值有没有变化,有变化继续修改值
                        if (!dataColumnMeta.equals(formColumnMeta)) {
                            alterList.add(alterSql + getUpdateColumnSql(formColumnMeta, dataColumnMeta));
                            if (DataBaseConstant.DB_TYPE_POSTGRE_SQL.equals(dataBaseType)) {
                                alterList.add(alterSql + getUpdateSpecialSql(formColumnMeta, dataColumnMeta));
                            }
                        }
                        // 判断注释是不是相同,修改注释
                        if (!DataBaseConstant.DB_TYPE_SQL_SERVER.equals(dataBaseType) && !dataColumnMeta.equalsComment(formColumnMeta)) {
                            alterList.add(getColumnCommentSql(formColumnMeta));
                        }
                    } else {
                        // 不包含就是要增加
                        alterList.add(alterSql + getAddColumnSql(formColumnMeta));
                        if (!DataBaseConstant.DB_TYPE_SQL_SERVER.equals(dataBaseType) && ConvertUtils.isNotEmpty(formColumnMeta.getColumnComment())) {
                            alterList.add(getColumnCommentSql(formColumnMeta));
                        }
                    }
                } else {
                    // 已经存在的判断是否修改了类型长度。。
                    // 判断是否类型、长度、是否为空、精度被修改，如果有修改则处理修改
                    ColumnMeta dataColumnMeta = dataBaseColumnMeta.get(columnName);
                    ColumnMeta formColumnMeta = tableFormColumnMeta.get(columnName);
                    // 如果不相同，则表示有变化，则需要修改
                    if (!dataColumnMeta.equalsByDataType(formColumnMeta, dataBaseType)) {
                        alterList.add(alterSql + getUpdateColumnSql(formColumnMeta, dataColumnMeta));
                    }
                    // 字段备注更新
                    if (!DataBaseConstant.DB_TYPE_SQL_SERVER.equals(dataBaseType) && !DataBaseConstant.DB_TYPE_ORACLE.equals(dataBaseType) && !dataColumnMeta.equalsComment(formColumnMeta)) {
                        alterList.add(getColumnCommentSql(formColumnMeta));
                    }
                }
            }
            // 删除数据库的列
            // 要判断这个列不是修改的
            for (String columnName : dataBaseColumnMeta.keySet()) {
                if ((!tableFormColumnMeta.containsKey(columnName.toLowerCase())) && (!newAndOldFieldMap.containsValue(columnName.toLowerCase()))) {
                    alterList.add(alterSql + getDropColumnSql(columnName));
                }
            }
        } catch (SQLException e) {
            CommonResponseEnum.ERROR_500.assertFail(e);
        }
        log.info(" database update sql : " + alterList.toString());
        return alterList;
    }

    /**
     * 获取创建指定表的指定索引 SQL
     *
     * @param tableIndex - 数据库表索引
     * @param tableName - 数据库表名称
     * @return String - SQL语句
     * @author XinLau
     * @creed The only constant is change ! ! !
     * @since 2020/10/3 9:57
     */
    public String creatIndexesSql(final TableIndex tableIndex, final String tableName) {
        return dataBaseTableHandleService.creatIndexes(tableIndex, tableName);
    }

    /**
     * 获取删除指定表的指定索引 SQL
     *
     * @param indexName - 索引名称
     * @param tableName - 数据库表名称
     * @return String - SQL语句
     * @author XinLau
     * @creed The only constant is change ! ! !
     * @since 2020/10/3 9:57
     */
    public String dropIndexesSql(String indexName, String tableName) {
        return dataBaseTableHandleService.dropIndexes(indexName, tableName);
    }

    /**
     * 获取指定表的指定索引 SQL
     *
     * @param indexName - 索引名称
     * @param tableName - 数据库表名称
     * @return String - SQL语句
     * @author XinLau
     * @creed The only constant is change ! ! !
     * @since 2020/10/3 9:57
     */
    public String getCountIndexSql(String indexName, String tableName) {
        return dataBaseTableHandleService.countIndex(indexName, tableName);
    }

}
